﻿using System;
using System.Collections.Generic;
using System.Linq;
using ClientTools.Library.DataTypes.Versions;
using ClientTools.Library.Manager.Helper;
using umbraco.BusinessLogic;
using umbraco.DataLayer;
using Version=ClientTools.Library.DataTypes.Versions.Version;

namespace ClientTools.Library.Manager
{
    public static class VersionManager 
    {
        private static ISqlHelper SqlHelper
        {
            get { return Application.SqlHelper; }
        }

        #region Delete Versions

        public static VersionInfo DeleteVersionsByDate(DateTime date, int nodeId)
        {
            if (nodeId == -1)
                return DeleteVersionsByDate(date);

            // initialize the returning object
            var returnValue = new VersionInfo();

            // first load all content elements
            //var contents = new List<Content>();
            //var reader = SqlHelper.ExecuteReader("SELECT * FROM cmsContent WHERE ");
            //while (reader.Read())
            //    contents.Add(new Content { Id = reader.GetInt("nodeId") });

            // and all versions
            var contentVersions = new List<ContentVersion>();
            var reader = SqlHelper.ExecuteReader("SELECT * FROM cmsContentVersion WHERE ContentId = @contentId",
                                                 SqlHelper.CreateParameter("@contentId", nodeId));
            while (reader.Read())
                contentVersions.Add(new ContentVersion
                {
                    Id = reader.GetInt("id"),
                    ContentId = reader.GetInt("ContentId"),
                    VersionId = reader.GetGuid("VersionId"),
                    VersionDate = reader.GetDateTime("VersionDate")
                });

            // and all documents
            var documents = new List<Document>();
            reader = SqlHelper.ExecuteReader("SELECT * FROM cmsDocument");
            while (reader.Read())
                documents.Add(new Document
                {
                    IsNewest = reader.GetBoolean("newest"),
                    IsPublished = reader.GetBoolean("published"),
                    NodeId = reader.GetInt("nodeId")
                });

            // get the versions of this content element sorted descending by VersionDate 
            var versions =
                (from x in contentVersions where x.ContentId == nodeId orderby x.VersionDate descending select x).ToList();
            // set the variable if a published version is found
            var bFoundFirstPublished = false;
            // for each version
            for (var i = 0; i < versions.Count; i++)
            {
                var selectedVersion = versions[i];
                // get the document of this version
                var selectedDocuments = (from x in documents where x.NodeId == nodeId && x.VersionId == selectedVersion.VersionId select x).ToList();

                // if there is no document leave this version in and go on with the loop
                if (selectedDocuments.Count() == 0)
                    continue;

                // if the published version isn't found yet
                if (!bFoundFirstPublished)
                {
                    // check if the actual version is published
                    if (selectedDocuments[0].IsPublished)
                        // if it is published leave this version in and set the variable that a published version is found
                        bFoundFirstPublished = true;

                    // and go on with the loop
                    continue;
                }

                // if we got here then check if the VersionDate is lower than the given date
                if (selectedVersion.VersionDate < date && !selectedDocuments[0].IsPublished)
                {
                    // if so then delete all property data, the document and the version itself and increment the counters for the return infos
                    var vi = DeleteVersion(selectedVersion.VersionId);
                    returnValue.PropertyData += vi.PropertyData;
                    returnValue.Documents += vi.Documents;
                    returnValue.Versions += vi.Versions;
                }
            }
            return returnValue;
        }

        public static VersionInfo DeleteVersionsByDate(DateTime date)
        {
            // initialize the returning object
            var returnValue = new VersionInfo();

            // first load all content elements
            var contents = new List<Content>();
            var reader = SqlHelper.ExecuteReader("SELECT * FROM cmsContent");
            while (reader.Read())
                contents.Add(new Content {Id = reader.GetInt("nodeId")});

            // and all versions
            var contentVersions = new List<ContentVersion>();
            reader = SqlHelper.ExecuteReader("SELECT * FROM cmsContentVersion");
            while (reader.Read())
                contentVersions.Add(new ContentVersion
                                        {
                                            Id = reader.GetInt("id"),
                                            ContentId = reader.GetInt("ContentId"),
                                            VersionId = reader.GetGuid("VersionId"),
                                            VersionDate = reader.GetDateTime("VersionDate")
                                        });

            // and all documents
            var documents = new List<Document>();
            reader = SqlHelper.ExecuteReader("SELECT * FROM cmsDocument");
            while (reader.Read())
                documents.Add(new Document
                                  {
                                      IsNewest = reader.GetBoolean("newest"),
                                      IsPublished = reader.GetBoolean("published"),
                                      NodeId = reader.GetInt("nodeId")
                                  });

            // for each content
            foreach (var content in contents)
            {
                var nodeId = content.Id;

                // get the versions of this content element sorted descending by VersionDate 
                var versions =
                    (from x in contentVersions where x.ContentId == nodeId orderby x.VersionDate descending select x).ToList();
                // set the variable if a published version is found
                var bFoundFirstPublished = false;
                // for each version
                for (var i = 0; i < versions.Count; i++)
                {
                    var selectedVersion = versions[i];
                    // get the document of this version
                    var selectedDocuments = (from x in documents where x.NodeId == nodeId && x.VersionId == selectedVersion.VersionId select x).ToList();

                    // if there is no document leave this version in and go on with the loop
                    if (selectedDocuments.Count() == 0)
                        continue;

                    // if the published version isn't found yet
                    if (!bFoundFirstPublished)
                    {
                        // check if the actual version is published
                        if (selectedDocuments[0].IsPublished)
                            // if it is published leave this version in and set the variable that a published version is found
                            bFoundFirstPublished = true;

                        // and go on with the loop
                        continue;
                    }
                    
                    // if we got here then check if the VersionDate is lower than the given date
                    if (selectedVersion.VersionDate < date && !selectedDocuments[0].IsPublished)
                    {
                        // if so then delete all property data, the document and the version itself and increment the counters for the return infos
                        var vi = DeleteVersion(selectedVersion.VersionId);
                        returnValue.PropertyData += vi.PropertyData;
                        returnValue.Documents += vi.Documents;
                        returnValue.Versions += vi.Versions;
                    }
                }
            }
            return returnValue;
        }

        //public VersionInfo DeleteVersionsByDate(DateTime date, int id, bool recursive)
        //{
        //    throw new NotImplementedException();
        //    // initialize the returning object
        //    //return new VersionInfo();

        //    ////    if (recursive)


        //    //var sb1 = new StringBuilder();
        //    //sb1.Append("SELECT * FROM cmsDocument WHERE VersionID IN (");
        //    //sb1.Append("SELECT VersionId FROM cmsContentVersion WHERE ContentId = {0} AND VersionDate < (");
        //    //sb1.Append("	SELECT cmsContentVersion.VersionDate FROM cmsDocument ");
        //    //sb1.Append("		INNER JOIN cmsContentVersion on cmsDocument.VersionId = cmsContentVersion.versionId");
        //    //sb1.Append("	WHERE nodeId = {0} AND published = 1)");
        //    //sb1.Append(")");

        //    //var sb2 = new StringBuilder();
        //    //sb2.Append("SELECT * FROM cmsContentVersion WHERE VersionID IN (");
        //    //sb2.Append("SELECT VersionId FROM cmsContentVersion WHERE ContentId = {0} AND VersionDate < (");
        //    //sb2.Append("	SELECT cmsContentVersion.VersionDate FROM cmsDocument ");
        //    //sb2.Append("		INNER JOIN cmsContentVersion on cmsDocument.VersionId = cmsContentVersion.versionId");
        //    //sb2.Append("	WHERE nodeId = {0} AND published = 1)");
        //    //sb2.Append(")");

        //    //var sb3 = new StringBuilder();
        //    //sb3.Append("SELECT * FROM cmsPropertyData WHERE VersionID IN (");
        //    //sb3.Append("SELECT VersionId FROM cmsContentVersion WHERE ContentId = {0} AND VersionDate < (");
        //    //sb3.Append("SELECT cmsContentVersion.VersionDate FROM cmsDocument ");
        //    //sb3.Append("INNER JOIN cmsContentVersion on cmsDocument.VersionId = cmsContentVersion.versionId");
        //    //sb3.Append("WHERE nodeId = {0} AND published = 1)");
        //    //sb3.Append(")");

        //    //// first load all content elements
        //    //var contents = new List<Content>();
        //    //var reader = SqlHelper.ExecuteReader("SELECT * FROM cmsContent");
        //    //while (reader.Read())
        //    //    contents.Add(new Content { Id = reader.GetInt("nodeId") });
            
        //    //DataSet dsContent = new DataSet();
        //    //SqlDataAdapter daContent = new SqlDataAdapter("SELECT * FROM cmsContent WHERE ", con);
        //    //daContent.Fill(dsContent);

        //    //    // and all versions
        //    //    SqlDataAdapter daContentVersion = new SqlDataAdapter("SELECT * FROM cmsContentVersion WHERE ", con);
        //    //    DataSet dsContentVersion = new DataSet();
        //    //    daContentVersion.Fill(dsContentVersion);

        //    //    // and all documents
        //    //    SqlDataAdapter daDocuments = new SqlDataAdapter("SELECT * FROM cmsDocument", con);
        //    //    DataSet dsDocuments = new DataSet();
        //    //    daDocuments.Fill(dsDocuments);

        //    //    // for each content
        //    //    foreach (DataRow drContent in dsContent.Tables[0].Rows)
        //    //    {
        //    //        int nodeId = (int)drContent["nodeID"];
        //    //        // get the versions of this content element sorted descending by VersionDate 
        //    //        DataRow[] drContentVersion = dsContentVersion.Tables[0].Select(string.Concat("ContentId = ", nodeId), "VersionDate DESC");
        //    //        // set the variable if a published version is found
        //    //        bool bFoundFirstPublished = false;
        //    //        // for each version
        //    //        for (int i = 0; i < drContentVersion.Length; i++)
        //    //        {
        //    //            Guid version = (Guid)drContentVersion[i]["versionID"];
        //    //            // get the document of this version
        //    //            DataRow[] drDocuments = dsDocuments.Tables[0].Select(string.Concat("nodeID = ", nodeId, " AND versionID = '", version, "'"));

        //    //            // if there is no document leave this version in and go on with the loop
        //    //            if ((drDocuments == null) || drDocuments.Length == 0)
        //    //            {
        //    //                continue;
        //    //            }
        //    //            DataRow drDocument = drDocuments[0];

        //    //            // if the published version isn't found yet
        //    //            if (!bFoundFirstPublished)
        //    //            {
        //    //                // check if the actual version is published
        //    //                if ((bool)drDocument["published"])
        //    //                {
        //    //                    // if it is published leave this version in and set the variable that a published version is found
        //    //                    bFoundFirstPublished = true;
        //    //                }
        //    //                // and go on with the loop
        //    //                continue;
        //    //            }
        //    //            // if we got here then check if the VersionDate is lower than the given date
        //    //            if (((DateTime)drContentVersion[i]["VersionDate"] < date) && (!(bool)drDocument["published"]))
        //    //            {
        //    //                // if so then delete all property data, the document and the version itself and increment the counters for the return infos
        //    //                returnValue.PropertyData += new SqlCommand("DELETE FROM cmsPropertyData WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
        //    //                returnValue.Documents += new SqlCommand("DELETE FROM cmsDocument WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
        //    //                returnValue.Versions += new SqlCommand("DELETE FROM cmsContentVersion WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
        //    //            }
        //    //        }
        //    //        returnValue.Content++;
        //    //    }
        //    //    return returnValue;
        //}

        public static VersionInfo DeleteVersionsByNumberOfItems(int numberOfItems, int nodeId)
        {
            if (nodeId == -1)
                return DeleteVersionsByNumberOfItems(numberOfItems, nodeId);

            // initialize the returning object
            var returnValue = new VersionInfo();

            ////// first load all content elements
            ////var contents = new List<Content>();
            ////var reader = SqlHelper.ExecuteReader("SELECT * FROM cmsContent");
            ////while (reader.Read())
            ////    contents.Add(new Content { Id = reader.GetInt("nodeId") });

            // and all versions
            var contentVersions = new List<ContentVersion>();
            var reader = SqlHelper.ExecuteReader("SELECT * FROM cmsContentVersion WHERE ContentId = @contentId",
                                                 SqlHelper.CreateParameter("@contentId", nodeId));
            while (reader.Read())
                contentVersions.Add(new ContentVersion
                {
                    Id = reader.GetInt("id"),
                    ContentId = reader.GetInt("ContentId"),
                    VersionId = reader.GetGuid("VersionId"),
                    VersionDate = reader.GetDateTime("VersionDate")
                });

            // and all documents
            var documents = new List<Document>();
            reader = SqlHelper.ExecuteReader("SELECT * FROM cmsDocument");
            while (reader.Read())
                documents.Add(new Document
                {
                    IsNewest = reader.GetBoolean("newest"),
                    IsPublished = reader.GetBoolean("published"),
                    NodeId = reader.GetInt("nodeId")
                });

            var versionsLeave = 0;

            // get the versions of this content element sorted descending by VersionDate 
            var versions =
                (from x in contentVersions where x.ContentId == nodeId orderby x.VersionDate descending select x).ToList();
            // set the variable if a published version is found
            var bFoundFirstPublished = false;
            // for each version
            for (var i = 0; i < versions.Count; i++)
            {
                var selectedVersion = versions[i];
                // get the document of this version
                var selectedDocuments = (from x in documents where x.NodeId == nodeId && x.VersionId == selectedVersion.VersionId select x).ToList();

                // if there is no document leave this version in and go on with the loop
                if (selectedDocuments.Count == 0)
                    continue;

                // if the published version isn't found yet
                if (!bFoundFirstPublished)
                {
                    // check if the actual version is published
                    if (selectedDocuments[0].IsPublished)
                    {
                        // if it is published leave this version in and set the variable that a published version is found
                        bFoundFirstPublished = true;
                        versionsLeave++;
                    }
                    // and go on with the loop
                    continue;
                }
                // if we got here then check if the number of items to leave is reached
                if ((versionsLeave > numberOfItems) && (!selectedDocuments[0].IsPublished))
                {
                    // if so then delete all property data, the document and the version itself and increment the counters for the return infos
                    var vi = DeleteVersion(selectedVersion.VersionId);
                    returnValue.PropertyData += vi.PropertyData;
                    returnValue.Documents += vi.Documents;
                    returnValue.Versions += vi.Versions;
                }
                // increment the version counter
                versionsLeave++;
            }
            return returnValue;
        }

        public static VersionInfo DeleteVersionsByNumberOfItems(int numberOfItems)
        {
            // initialize the returning object
            var returnValue = new VersionInfo();

            // first load all content elements
            var contents = new List<Content>();
            var reader = SqlHelper.ExecuteReader("SELECT * FROM cmsContent");
            while (reader.Read())
                contents.Add(new Content { Id = reader.GetInt("nodeId") });

            // and all versions
            var contentVersions = new List<ContentVersion>();
            reader = SqlHelper.ExecuteReader("SELECT * FROM cmsContentVersion");
            while (reader.Read())
                contentVersions.Add(new ContentVersion
                {
                    Id = reader.GetInt("id"),
                    ContentId = reader.GetInt("ContentId"),
                    VersionId = reader.GetGuid("VersionId"),
                    VersionDate = reader.GetDateTime("VersionDate")
                });

            // and all documents
            var documents = new List<Document>();
            reader = SqlHelper.ExecuteReader("SELECT * FROM cmsDocument");
            while (reader.Read())
                documents.Add(new Document
                {
                    IsNewest = reader.GetBoolean("newest"),
                    IsPublished = reader.GetBoolean("published"),
                    NodeId = reader.GetInt("nodeId")
                });

            // for each content
            foreach (var content in contents)
                //foreach (DataRow drContent in dsContent.Tables[0].Rows)
            {
                var nodeId = content.Id;
                var versionsLeave = 0;
                
                // get the versions of this content element sorted descending by VersionDate 
                var versions =
                    (from x in contentVersions where x.ContentId == nodeId orderby x.VersionDate descending select x).ToList();
                // set the variable if a published version is found
                var bFoundFirstPublished = false;
                // for each version
                for (var i = 0; i < versions.Count; i++)
                {
                    var selectedVersion = versions[i];
                    // get the document of this version
                    var selectedDocuments = (from x in documents where x.NodeId == nodeId && x.VersionId == selectedVersion.VersionId select x).ToList();

                    // if there is no document leave this version in and go on with the loop
                    if (selectedDocuments.Count == 0)
                        continue;

                    // if the published version isn't found yet
                    if (!bFoundFirstPublished)
                    {
                        // check if the actual version is published
                        if (selectedDocuments[0].IsPublished)
                        {
                            // if it is published leave this version in and set the variable that a published version is found
                            bFoundFirstPublished = true;
                            versionsLeave++;
                        }
                        // and go on with the loop
                        continue;
                    }
                    // if we got here then check if the number of items to leave is reached
                    if ((versionsLeave > numberOfItems) && (!selectedDocuments[0].IsPublished))
                    {
                        // if so then delete all property data, the document and the version itself and increment the counters for the return infos
                        var vi = DeleteVersion(selectedVersion.VersionId);
                        returnValue.PropertyData += vi.PropertyData;
                        returnValue.Documents += vi.Documents;
                        returnValue.Versions += vi.Versions;
                    }
                    // increment the version counter
                    versionsLeave++;
                }
            }
            return returnValue;
        }

        /// <summary>
        /// Deletes the given version and returns the informations how many rows have been deleted
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public static VersionInfo DeleteVersion(Guid guid)
        {
            // initialize the returning object
            var returnValue = new VersionInfo();
            
            // deletes the given version
            returnValue.PropertyData +=
                SqlHelper.ExecuteScalar<int>("DELETE FROM cmsPropertyData WHERE VersionId = @versionId",
                                             SqlHelper.CreateParameter("@versionId", guid));
            returnValue.Documents +=
                SqlHelper.ExecuteScalar<int>("DELETE FROM cmsDocument WHERE VersionId = @versionId",
                                             SqlHelper.CreateParameter("@versionId", guid));
            returnValue.Versions +=
                SqlHelper.ExecuteScalar<int>("DELETE FROM cmsContentVersion WHERE VersionId = @versionId",
                                             SqlHelper.CreateParameter("@versionId", guid));

            return returnValue;
        }

        #endregion

        #region Get Versions

        /// <summary>
        /// Gets all versions from the given node
        /// </summary>
        /// <param name="nodeId"></param>
        /// <returns></returns>
        public static VersionCollection GetVersions(int nodeId)
        {
            var returnValue = new VersionCollection();
            var reader = SqlHelper.ExecuteReader(
                "SELECT nodeId, updateDate, cmsDocument.versionId, newest, published, [text], documentUser, userName, VersionDate FROM cmsDocument INNER JOIN umbracoUser ON cmsDocument.documentUser = umbracoUser.id INNER JOIN cmsContentVersion ON cmsDocument.versionId = cmsContentVersion.VersionId WHERE nodeId = @nodeId ORDER BY versionDate DESC",
                SqlHelper.CreateParameter("@nodeId", nodeId));
            
            while (reader.Read())
                returnValue.Add(new Version
                                    {
                                        Id = reader.GetInt("nodeId"),
                                        UpdateDate = reader.GetDateTime("updateDate"),
                                        Guid = reader.GetGuid("versionId"),
                                        IsNewest = reader.GetBoolean("newest"),
                                        Published = reader.GetBoolean("published"),
                                        Text = reader.GetString("text"),
                                        UserId = reader.GetInt("documentUser"),
                                        UserName = reader.GetString("userName"),
                                        VersionDate = reader.GetDateTime("VersionDate")
                                    });

            return returnValue;
        }

        /// <summary>
        /// Gets the version informations of the given node. If includeChildren is set it adds the informations of the child nodes
        /// </summary>
        /// <param name="nodeId"></param>
        /// <param name="includeChildren"></param>
        /// <returns></returns>
        public static VersionInfo GetVersionInfos(int nodeId, bool includeChildren)
        {
            var returnValue = GetVersionInfos(nodeId);
            if (includeChildren)
            {
                var childrenInfos = GetChildrenVersionInfos(nodeId);
                returnValue.Documents += childrenInfos.Documents;
                returnValue.Versions += childrenInfos.Versions;
                returnValue.PropertyData += childrenInfos.PropertyData;
            }
            return returnValue;
        }

        /// <summary>
        /// Gets the version informations from the given node
        /// </summary>
        /// <param name="nodeId"></param>
        /// <returns></returns>
        private static VersionInfo GetVersionInfos(int nodeId)
        {
            return new VersionInfo
                       {
                           Documents =
                               SqlHelper.ExecuteScalar<int>(
                               "SELECT COUNT(*) FROM cmsDocument WHERE nodeId = @nodeId",
                               SqlHelper.CreateParameter("@nodeId", nodeId)),
                           PropertyData =
                               SqlHelper.ExecuteScalar<int>(
                               "SELECT COUNT(*) FROM cmsPropertyData WHERE contentNodeId = @nodeId",
                               SqlHelper.CreateParameter("@nodeId", nodeId)), 
                           Versions = 
                               SqlHelper.ExecuteScalar<int>(
                               "SELECT COUNT(*) FROM cmsContentVersion WHERE ContentId = @nodeId",
                               SqlHelper.CreateParameter("@nodeId", nodeId))
                       };
        }

        /// <summary>
        /// Gets the version informations from all children of the given node
        /// </summary>
        /// <param name="nodeId"></param>
        /// <returns></returns>
        public static VersionInfo GetChildrenVersionInfos(int nodeId)
        {
            var returnValue = new VersionInfo();

            var sql = "SELECT COUNT(*) FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' AND [path] LIKE '%,{0}%' AND id <> {0}) AND published = 1";
            returnValue.Documents = SqlHelper.ExecuteScalar<int>(string.Format(sql, nodeId));

            sql = "SELECT COUNT(*) FROM cmsDocument WHERE nodeId in (SELECT nodeId FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' AND [path] LIKE '%,{0}%' AND id <> {0}) AND published = 1)";
            returnValue.Versions = SqlHelper.ExecuteScalar<int>(string.Format(sql, nodeId));

            sql = "SELECT COUNT(*) FROM cmsPropertyData WHERE contentNodeId in (SELECT nodeId FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' AND [path] LIKE '%,{0}%' AND id <> {0}) AND published = 1)";
            returnValue.PropertyData = SqlHelper.ExecuteScalar<int>(string.Format(sql, nodeId));

            return returnValue;
        }

        /// <summary>
        /// Gets the version informations from all published nodes
        /// </summary>
        /// <returns></returns>
        public static VersionInfo GetVersionInfos()
        {
            return new VersionInfo
                       {
                           Documents =
                               SqlHelper.ExecuteScalar<int>(
                               "SELECT COUNT(*) FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972') AND published = 1"),
                           Versions =
                               SqlHelper.ExecuteScalar<int>(
                               "SELECT COUNT(*) FROM cmsDocument WHERE nodeId in (SELECT nodeId FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972') AND published = 1)"),
                           PropertyData =
                               SqlHelper.ExecuteScalar<int>(
                               "SELECT COUNT(*) FROM cmsPropertyData WHERE contentNodeId in (SELECT nodeId FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972') AND published = 1)")
                       };
        }

        #endregion
    }
}